package nc.bs.hrppl.pjapplication.ace.bp.rule;

public class FourXMBusinessSQL {


    /**
     * 过滤项目查询
     * @param officedept
     * @param pk_org
     * @param applyyear
     * @return
     */
    public static String getFourXMProjectSQL(Object officedept, Object pk_org, Object applyyear){
        FourXMBusinessSQL util = new FourXMBusinessSQL();
        StringBuffer sql = new StringBuffer();
        // 终止采购的项目不允许在进行申请  四级采购项目状态---> 终止采购 : 4
        sql.append("  and approvestatus  = 1 ");
        sql.append(" and def1 in ( "); // def1
        // 获取获取四级采购项目中对一个财务项目
        String buildsql = util.buildBaseDataContrastTables("CG", "b26fa3cb-4087-4027-a3b6-c83ab2a086a9", pk_org, officedept);
        sql.append(buildsql);
        sql.append(" ) ");
        // 一个采购项目只允许申请一次
        sql.append(util.buildQueryOneSQL(applyyear));
    	return sql.toString();
    }

    /**
     * 获取部门对照关系sql
     * @param exsystemcode
     * @param bdclass
     * @param pk_org
     * @param pk_dept
     * @return
     */
    protected String buildBaseDataContrastTables(Object exsystemcode, Object bdclass,Object pk_org, Object pk_dept) {
        String sql = " select  " +
                "    dzb.exsysval sfcode " +
                " from xx_exsystem xe  " +
                " left join xx_bdcontra dzh on xe.pk_exsystem = dzh.exsystem " +
                " left join xx_bdcontra_b dzb on dzh.pk_contra = dzb.pk_contra " +
                " left join org_dept dept on dzb.bdcode = dept.code " +
                " where 1 = 1 and xe.dr =  0 " +
                "    and dzh.dr = 0 and dzb.dr = 0  " +
                "    and  xe.exsystemcode = '" + exsystemcode + "'  " +
                "    and dzh.bdclass = '" + bdclass + "' " +
                "    and dept.pk_dept  = '" + pk_dept + "' " +
                "    and dzh.pk_org = '" + pk_org + "' ";
        return sql;
    }

    /**
     * 一个采购项目只允许申请一次
     * @return
     */
    protected String buildQueryOneSQL(Object year) {
        StringBuilder sql = new StringBuilder();
        sql.append(" and pk_sjxm in (   " );
        sql.append("  SELECT  " );
        sql.append("    h.pk_sjxm  " );
        sql.append("  FROM  " );
        sql.append("    hrpbm_sjxmh h  " );
        sql.append("  left JOIN hrpbm_sjxmb b on h.pk_sjxm = b.pk_sjxm  " );
        sql.append("  where 1 = 1  and b.bdef1 <> 'Y' and b.ysyear = '"+year+"'  " );
        sql.append(" )");
        return sql.toString();
    }
}
